﻿using IncidentesBE;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace IncidentesADO
{
    public class Fnc_FuncionariosADO
    {
        ConexionADO MiConexion = new ConexionADO();
        SqlConnection cnx = new SqlConnection();
        SqlCommand cmd = new SqlCommand();
        DataView dtv = new DataView();
        bool vexito;

        public DataTable ListarFnc_Funcionarios_All()
        {
            DataSet dts = new DataSet();
            try
            {
                cnx.ConnectionString = MiConexion.GetCnx();
                cmd.Connection = cnx;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_ListarFnc_Funcionarios_All";
                SqlDataAdapter miada = default(SqlDataAdapter);
                miada = new SqlDataAdapter(cmd);
                miada.Fill(dts, "Sistemas");
                dtv = dts.Tables["Sistemas"].DefaultView;
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (cnx.State == ConnectionState.Open)
                {
                    cnx.Close();
                }
                cmd.Parameters.Clear();
            }
            return dts.Tables["Sistemas"];
        }
        public List<Fnc_FuncionariosBE> ListarFnc_FuncionariosO_Act()
        {
            string conexion = MiConexion.GetCnx();
            List<Fnc_FuncionariosBE> lFnc_FuncionariosBE = null;
            try
            {
                SqlConnection con = new SqlConnection(conexion);
                con.Open();
                SqlCommand cmd = new SqlCommand("sp_ListarFnc_FuncionariosBE_Act", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataReader drd = cmd.ExecuteReader(CommandBehavior.SingleResult);
                if (drd != null)
                {
                    lFnc_FuncionariosBE = new List<Fnc_FuncionariosBE>();

                    Fnc_FuncionariosBE obeFuncionariosBE = null;
                    while (drd.Read())
                    {
                        ReadSingleRow((IDataRecord)drd);
                        obeFuncionariosBE = new Fnc_FuncionariosBE();
                        obeFuncionariosBE.Funcionario_Id = Convert.ToInt16(drd[0]);
                        obeFuncionariosBE.Funcionario_Nome = drd[1].ToString();
                        obeFuncionariosBE.Funcionario_Drt = drd[2].ToString();
                        obeFuncionariosBE.Funcionario_Status = Convert.ToByte(drd[3]);
                        obeFuncionariosBE.Funcionario_Email = drd[4].ToString();
                        obeFuncionariosBE.Funcionario_Tnumber = drd[5].ToString();
                        obeFuncionariosBE.Funcionario_CPF = drd[6].ToString();
                        obeFuncionariosBE.Area_Id = Convert.ToInt32(drd[7]);
                        obeFuncionariosBE.Grupo_Id = Convert.ToInt16(drd[8]);
                        lFnc_FuncionariosBE.Add(obeFuncionariosBE);
                    }
                    drd.Close();
                }
            }
            catch(SqlException ex)
            {

            }
            catch (Exception ex)
            {

            }
            return (lFnc_FuncionariosBE);
        }
        private static void ReadSingleRow(IDataRecord record)
        {
            Console.WriteLine(String.Format("{0}, {1}", record[0], record[1]));
        }
        public DataTable ListarFnc_Funcionarios_Act()
        {
            DataSet dts = new DataSet();
            try
            {
                cnx.ConnectionString = MiConexion.GetCnx();
                cmd.Connection = cnx;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_ListarFnc_Funcionarios_Act";
                SqlDataAdapter miada = default(SqlDataAdapter);
                miada = new SqlDataAdapter(cmd);
                miada.Fill(dts, "Sistemas");
                dtv = dts.Tables["Sistemas"].DefaultView;
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (cnx.State == ConnectionState.Open)
                {
                    cnx.Close();
                }
                cmd.Parameters.Clear();
            }
            return dts.Tables["Sistemas"];
        }
        public bool DeshabilitarFuncionario(int _vcod)
        {
            cnx.ConnectionString = MiConexion.GetCnx();
            cmd.Connection = cnx;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_Fnc_Funcionario_Status";

            try
            {
                cmd.Parameters.Add(new SqlParameter("@vcod", SqlDbType.Int));
                cmd.Parameters["@vcod"].Value = _vcod;
                cmd.Parameters.Add(new SqlParameter("@status", SqlDbType.Int));
                cmd.Parameters["@status"].Value = 0;
                cnx.Open();
                cmd.ExecuteNonQuery();
                vexito = true;

            }
            catch (SqlException x)
            {
                vexito = false;
            }
            finally
            {
                if (cnx.State == ConnectionState.Open)
                {
                    cnx.Close();
                }
                cmd.Parameters.Clear();
            }

            return vexito;
        }
        public bool HabilitarFuncionario(int _vcod)
        {
            cnx.ConnectionString = MiConexion.GetCnx();
            cmd.Connection = cnx;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_Fnc_Funcionario_Status";

            try
            {
                cmd.Parameters.Add(new SqlParameter("@vcod", SqlDbType.Int));
                cmd.Parameters["@vcod"].Value = _vcod;
                cmd.Parameters.Add(new SqlParameter("@status", SqlDbType.Int));
                cmd.Parameters["@status"].Value = 1;
                cnx.Open();
                cmd.ExecuteNonQuery();
                vexito = true;

            }
            catch (SqlException x)
            {
                vexito = false;
            }
            finally
            {
                if (cnx.State == ConnectionState.Open)
                {
                    cnx.Close();
                }
                cmd.Parameters.Clear();
            }

            return vexito;
        }
        public DataTable BuscarFnc_FuncionarioByNombre(string vnom)
        {
            DataSet dts = new DataSet();
            try
            {
                cnx.ConnectionString = MiConexion.GetCnx();
                cmd.Connection = cnx;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_BuscarFnc_FuncionarioByNombre";
                //Agregamos el parametro para el SP
                cmd.Parameters.Add(new SqlParameter("@texto", SqlDbType.VarChar, 200));
                cmd.Parameters["@texto"].Value = vnom;
                SqlDataAdapter miada = default(SqlDataAdapter);
                miada = new SqlDataAdapter(cmd);
                miada.Fill(dts, "Turno");
                dtv = dts.Tables["Turno"].DefaultView;
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (cnx.State == ConnectionState.Open)
                {
                    cnx.Close();
                }
                cmd.Parameters.Clear();
            }
            return dts.Tables["Turno"];
        }
        public Fnc_FuncionariosBE LoguearFuncionario(string vusuario, string vpass)
        {
            SqlDataReader dtr = default(SqlDataReader);
            Fnc_FuncionariosBE _Fnc_FuncionariosBE = new Fnc_FuncionariosBE();
            DataSet dts = new DataSet();
            try
            {
                cnx.ConnectionString = MiConexion.GetCnx();
                cmd.Connection = cnx;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_LoguearUsuario";
                cmd.Parameters.Add(new SqlParameter("@vusuario", SqlDbType.VarChar, 20));
                cmd.Parameters["@vusuario"].Value = vusuario;
                cmd.Parameters.Add(new SqlParameter("@vpass", SqlDbType.VarChar, 20));
                cmd.Parameters["@vpass"].Value = vpass;
                cnx.Open();
                dtr = cmd.ExecuteReader();
                if (dtr.HasRows == true)
                {
                    dtr.Read();
                    var _with1 = _Fnc_FuncionariosBE;
                    _with1.Funcionario_Id = Convert.ToInt16(dtr.GetValue(dtr.GetOrdinal("Funcionario_Id")));
                    _with1.Funcionario_Nome = dtr.GetValue(dtr.GetOrdinal("Funcionario_Nome")).ToString();
                    _with1.Funcionario_Email = dtr.GetValue(dtr.GetOrdinal("Funcionario_Email")).ToString();
                    _with1.Funcionario_Drt = dtr.GetValue(dtr.GetOrdinal("Funcionario_Drt")).ToString();
                    _with1.Area_Id = Convert.ToInt32(dtr.GetValue(dtr.GetOrdinal("Area_Id")));
                    _with1.Grupo_Id = Convert.ToInt16(dtr.GetValue(dtr.GetOrdinal("Grupo_id")));
                }
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (cnx.State == ConnectionState.Open)
                {
                    cnx.Close();
                }
                cmd.Parameters.Clear();
            }
            return _Fnc_FuncionariosBE;
        }
        public Fnc_FuncionariosBE TraerFnc_Funcionario(int _Funcionario_id)
        {
            SqlDataReader dtr = default(SqlDataReader);
            Fnc_FuncionariosBE _Fnc_FuncionariosBE = new Fnc_FuncionariosBE();
            DataSet dts = new DataSet();
            try
            {
                cnx.ConnectionString = MiConexion.GetCnx();
                cmd.Connection = cnx;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_TraerFnc_FuncionarioById";
                cmd.Parameters.Add(new SqlParameter("@Funcionario_id", SqlDbType.VarChar, 20));
                cmd.Parameters["@Funcionario_id"].Value = _Funcionario_id;
                cnx.Open();
                dtr = cmd.ExecuteReader();
                if (dtr.HasRows == true)
                {
                    dtr.Read();
                    var _with1 = _Fnc_FuncionariosBE;
                    _with1.Funcionario_Id = Convert.ToInt16(dtr.GetValue(dtr.GetOrdinal("Funcionario_Id")));
                    _with1.Funcionario_Nome = dtr.GetValue(dtr.GetOrdinal("Funcionario_Nome")).ToString();
                    _with1.Funcionario_Email = dtr.GetValue(dtr.GetOrdinal("Funcionario_Email")).ToString();
                    _with1.Funcionario_Drt = dtr.GetValue(dtr.GetOrdinal("Funcionario_Drt")).ToString();
                    _with1.Funcionario_CPF = dtr.GetValue(dtr.GetOrdinal("Funcionario_CPF")).ToString();
                    _with1.Area_Id = Convert.ToInt32(dtr.GetValue(dtr.GetOrdinal("Area_Id")));
                    _with1.Grupo_Id = Convert.ToInt16(dtr.GetValue(dtr.GetOrdinal("Grupo_Id")));
                }
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (cnx.State == ConnectionState.Open)
                {
                    cnx.Close();
                }
                cmd.Parameters.Clear();
            }
            return _Fnc_FuncionariosBE;
        }
    }
}
